Create database HospitalDB

Create table Patients
(
PatientID int,
PatientSSN varchar (9),
PatientLName varchar (30),
PatientFName varchar (20),
PatientMName varchar (10),
Gender varchar (1),
DOB int,
Patientht varchar (3),
PatientWt int,
PhoneHM varchar (10),
PhoneCell varchar (10),
PhoneWk varchar (10),
PatientAddr varchar (20),
PatientCity varchar (15),
PatientState varchar (2),
PatientZip int,
PatientCounty varchar (15),
PatientEmail varchar (20)
)
Insert into Patients
(PatientID, PatientSSN, PatientLName, PatientFName, PatientMName, Gender, DOB, Patientht, Patientwt, phoneHM, PHoneCell, PhoneWk, PatientAddr, PatientCity, PatientState, PatientZip, PatientCounty, PatientEmail)
Values
('100002','552137458','Hernandez','Noe','B','M','19950922','67','190','7142937815','','','123 Pear Ridge Dr','Costa Mesa','CA','92626','Orange','yallcrazy@msn.com'),
('100003','715223848','Lezama','Gustavo','C','M','19900621','61','150','3105271919','','','3055 Main St','Santa Ana','CA','92707','Orange','Mznunu@hotmail.com'),
('100005','835351897','Pantoja','Jose','E','M','19990819','70','250','6573371981','','','2301 Bristol St','Santa Ana','CA','92706','Orange','JosePanto@yahoo.com'),
('100004','988135725','Bravo','Juan','D','M','19960615','63','175','8185279175','','','727 Minnie St','Santa Ana','CA','92701','Orange','JuanBravo@yahoo.com'),
('100001','321523177','Reyes','Jose','A','M','19820228','65','175','9492372154','9494520781','7142234714','1301 Orange Ave','Garden Grove','CA','92632','Orange','Dezz@yahoo.com')

Create Table PatientRecord
(
RecordID int,
PatientID varchar (10),
Reason varchar (35),
Date datetime,
EmployeeID varchar (10),
Diagnosis varchar (35),
Recommendation varchar (35)
)


Create Table PatientInsurance
(
PatientID varchar (10),
InsPolicyNum varchar (15),
InsName varchar (20),
InsAddr varchar (20),
InsCity varchar (15),
InsState varchar (2),
InsZip int,
InsPhone int,
InsFax int
)

Insert into PatientInsurance
(PatientID, InsPolicyNum, InsName, InsAddr, InsCity, InsState, InsZip, InsPhone, InsFax)
value
('100001','846568463418','Aetna Medical','2625 Orange Trail','Walnut Creek','CA','94596','8008723862',''),
('100002','548654686465','Baller Intl','123 Poppy St','Torrance','CA','3105846287',''),
('100003','527165269755','Adrianas','4860 Tustin Blvd','Costa Mesa','CA','92620','7145052457',''),
('100004','654989635137','Kaiser Inc','6 Madison','Irvine','CA','92604','9492625780',''),
('100005','789846351688','Blue Cross','3080 Main St','Santa Ana','CA','92626','7144292700','')


Create Table Doctors
(
DoctorID varchar (10),
DOctorFName varchar (30),
DoctorMName varchar (10),
DOctorLName varchar (20),
DoctorAddr varchar (20),
DoctorCity varchar (15),
DoctorState varchar (2),
DoctorZip int (5)
HireDate date
)

Create Table DoctorSpecialty
(
RecordID int (5),
SpecID varchar (5),
DoctorID varchar (10)
)

Create Table SpecialtyChart
(
RecordID int (10),
SpecID varchar (5),
Specialty varchar (20),
Description varchar (50)
)

Create table Billing
(
RecordID varchar (10),
BillingID varchar (10),
PatientID varchar (10),
Date date,
BIllPaid date,
AmountPaid int (10),
CopayDue int (10)
)

insert into Billing
(RecordID, BillingID, PatientID, Date, BillPaid, AmountPaid, CopayDue)
value
('0000001','20151105001','100001','20151101','20151115','3975.50','50'),
('0000002','20150612001','100002','20150704','20150704','175.00','60')


Create Table PatientBill
(
RecordID varchar (10),
PatientID varchar (10),
BillingID varchar (10),
ItemID varchar (10),
ItemQty int (10),
ItemCost int (10),
DateChrg date
)


Inset into PatientBill
(RecordID, PatientID, BillingID, ItemID, ItemQty, ItemCost, DateChrg)
value
('000001','100001','20151105001','00129','1','1.25','20151105'),
('000002','100001','20151105001','00127','30','.12','20151105'),
('000003','100001','20151105001','00134','30','.57','20151105'),
('000004','100001','20151105001','00064','90','.95','20151105'),
('000005','100001','20151105001','00529','1','22.95','20151105'),
('000006','100001','20151105001','00374','1','175.00','20151105'),
('000007','100002','20150612001','00074','45','.80','20150715'),
('000008','100002','20150612001','00841','21','.58','20150715'),
('000009','100002','20150612001','00481','90','.79','20150715'),
('000010','100002','20150612001','00042','1','75.00','20150715'),
('000011','100002','20150612001','00811','30','1.85','20150715'),
('000012','100002','20150612001','00330','1','29.95','20150715')


Create Table HospitalRooms
(
RoomID varchar (10),
Bldg varchar (5),
Flr int (3),
Rm int (3),
RmType (10)
)


Create table Inpatient
(
PatientID varchar (10),
Admission date,
Discharge date,
DoctorID varchar (10)
)

Create Table Employees
(
EmployeeID varchar (10),
EmployeeSSN varchar (10),
EmployeeFName varchar (30),
EmployeeMName varchar (20),
EmployeeLName varchar (15),
EmployeeGender varchar (1),
Designation varchar (20),
EmployeeAddr varchar (30),
EmployeeCity varchar (10),
EmployeeState varchar (2),
EmployeeZip varchar (5),
EMployeeCounty varchar (10),
PhoneHm int (10),
PhoneWk Int (10),
PhoneCell int (10)
)